import { Router, Request, Response } from 'express'
import { getDatabase } from '../database/init'
import { authenticateToken } from '../middleware/auth'
import { 
  getUserKanbanColumns, 
  updateTaskStatus, 
  batchUpdateTaskPositions,
  TaskPositionUpdate 
} from '../database/kanbanMigration'

const router = Router()

// 所有看板路由都需要认证
router.use(authenticateToken)

// 获取看板数据
router.get('/', async (req: Request, res: Response) => {
  try {
    const userId = req.userId!
    const db = getDatabase()
    
    // 获取看板列配置
    const columns = await getUserKanbanColumns(db, userId)
    
    // 获取用户的所有任务，按状态和位置排序
    const tasksResult = await db.query(`
      SELECT 
        t.*,
        COALESCE(
          json_agg(
            CASE WHEN s.id IS NOT NULL THEN 
              json_build_object('id', s.id, 'title', s.title, 'completed', s.completed)
            END
          ) FILTER (WHERE s.id IS NOT NULL), 
          '[]'
        ) as subtasks
      FROM tasks t
      LEFT JOIN subtasks s ON t.id = s.task_id
      WHERE t.user_id = $1
      GROUP BY t.id
      ORDER BY 
        CASE t.status 
          WHEN 'todo' THEN 0
          WHEN 'in_progress' THEN 1 
          WHEN 'review' THEN 2
          WHEN 'done' THEN 3
          ELSE 4
        END,
        t.board_position ASC,
        t.created_at DESC
    `, [userId])
    
    // 转换任务数据格式
    const tasks = tasksResult.rows.map((row: any) => {
      const { 
        due_date, created_at, updated_at, user_id, board_position,
        is_recurring, recurring_type, recurring_pattern, parent_recurring_id,
        ...cleanRow 
      } = row
      
      return {
        ...cleanRow,
        dueDate: due_date,
        createdAt: created_at,
        updatedAt: updated_at,
        userId: user_id,
        boardPosition: board_position,
        isRecurring: is_recurring,
        recurringType: recurring_type,
        recurringPattern: recurring_pattern,
        parentRecurringId: parent_recurring_id
      }
    })
    
    // 按状态分组任务 - 确保所有状态都有初始化的数组
    const tasksByStatus = tasks.reduce((acc: any, task: any) => {
      const status = task.status || 'todo'
      if (!acc[status]) {
        acc[status] = []
      }
      acc[status].push(task)
      return acc
    }, {
      // 初始化所有可能的状态
      todo: [],
      in_progress: [],
      review: [],
      done: []
    })
    
    // 为每个列添加任务计数
    const columnsWithCount = columns.map((column: any) => ({
      ...column,
      taskCount: tasksByStatus[column.status]?.length || 0
    }))
    
    res.json({
      success: true,
      data: {
        columns: columnsWithCount,
        tasks: tasksByStatus
      }
    })
    
  } catch (error) {
    console.error('获取看板数据失败:', error)
    res.status(500).json({ 
      success: false, 
      error: '获取看板数据失败',
      message: (error as Error).message 
    })
  }
})

// 更新任务状态
router.patch('/tasks/:id/status', async (req: Request, res: Response) => {
  try {
    const { id: taskId } = req.params
    const { status, position = 0 } = req.body
    const userId = req.userId!
    
    if (!status) {
      return res.status(400).json({
        success: false,
        error: '状态不能为空'
      })
    }
    
    const validStatuses = ['todo', 'in_progress', 'review', 'done']
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        error: '无效的状态值'
      })
    }
    
    const db = getDatabase()
    const updatedTask = await updateTaskStatus(db, taskId, userId, status, position)
    
    res.json({
      success: true,
      message: '任务状态更新成功',
      data: updatedTask
    })
    
  } catch (error) {
    console.error('更新任务状态失败:', error)
    res.status(500).json({
      success: false,
      error: '更新任务状态失败',
      message: (error as Error).message
    })
  }
})

// 批量更新任务位置
router.patch('/reorder', async (req: Request, res: Response) => {
  try {
    const { updates } = req.body as { updates: TaskPositionUpdate[] }
    const userId = req.userId!
    
    if (!Array.isArray(updates) || updates.length === 0) {
      return res.status(400).json({
        success: false,
        error: '更新数据不能为空'
      })
    }
    
    // 验证更新数据格式
    for (const update of updates) {
      if (!update.taskId || !update.status || typeof update.position !== 'number') {
        return res.status(400).json({
          success: false,
          error: '更新数据格式不正确'
        })
      }
    }
    
    const db = getDatabase()
    const result = await batchUpdateTaskPositions(db, userId, updates)
    
    res.json({
      success: true,
      message: `成功更新 ${result.updated} 个任务`,
      data: result
    })
    
  } catch (error) {
    console.error('批量更新任务位置失败:', error)
    res.status(500).json({
      success: false,
      error: '批量更新任务位置失败',
      message: (error as Error).message
    })
  }
})

// 获取看板列配置
router.get('/columns', async (req: Request, res: Response) => {
  try {
    const userId = req.userId!
    const db = getDatabase()
    
    const columns = await getUserKanbanColumns(db, userId)
    
    res.json({
      success: true,
      data: columns
    })
    
  } catch (error) {
    console.error('获取看板列配置失败:', error)
    res.status(500).json({
      success: false,
      error: '获取看板列配置失败',
      message: (error as Error).message
    })
  }
})

// 更新看板列配置
router.patch('/columns/:id', async (req: Request, res: Response) => {
  try {
    const { id: columnId } = req.params
    const { title, color, is_visible } = req.body
    const userId = req.userId!
    const db = getDatabase()
    
    const result = await db.query(`
      UPDATE kanban_columns 
      SET 
        title = COALESCE($1, title),
        color = COALESCE($2, color),
        is_visible = COALESCE($3, is_visible),
        updated_at = NOW()
      WHERE id = $4 AND user_id = $5
      RETURNING *
    `, [title, color, is_visible, columnId, userId])
    
    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        error: '看板列不存在或无权访问'
      })
    }
    
    res.json({
      success: true,
      message: '看板列配置更新成功',
      data: result.rows[0]
    })
    
  } catch (error) {
    console.error('更新看板列配置失败:', error)
    res.status(500).json({
      success: false,
      error: '更新看板列配置失败',
      message: (error as Error).message
    })
  }
})

// 获取看板统计数据
router.get('/stats', async (req: Request, res: Response) => {
  try {
    const userId = req.userId!
    const db = getDatabase()
    
    const statsResult = await db.query(`
      SELECT 
        status,
        COUNT(*) as count,
        COUNT(CASE WHEN priority = 'P0' THEN 1 END) as p0_count,
        COUNT(CASE WHEN priority = 'P1' THEN 1 END) as p1_count,
        COUNT(CASE WHEN priority = 'P2' THEN 1 END) as p2_count,
        COUNT(CASE WHEN priority = 'P3' THEN 1 END) as p3_count
      FROM tasks 
      WHERE user_id = $1
      GROUP BY status
      ORDER BY 
        CASE status 
          WHEN 'todo' THEN 0
          WHEN 'in_progress' THEN 1 
          WHEN 'review' THEN 2
          WHEN 'done' THEN 3
          ELSE 4
        END
    `, [userId])
    
    const stats = statsResult.rows.reduce((acc: any, row: any) => {
      acc[row.status] = {
        total: parseInt(row.count),
        p0: parseInt(row.p0_count),
        p1: parseInt(row.p1_count),
        p2: parseInt(row.p2_count),
        p3: parseInt(row.p3_count)
      }
      return acc
    }, {})
    
    res.json({
      success: true,
      data: stats
    })
    
  } catch (error) {
    console.error('获取看板统计失败:', error)
    res.status(500).json({
      success: false,
      error: '获取看板统计失败',
      message: (error as Error).message
    })
  }
})

export { router as kanbanRoutes }
